"""
Case Type   : security-auditing
Case Name   : 开启SET审计功能，设置audit_set_parameter=1
Create At   : 2024/7/4
Owner       : wan005
Description :
    1.登录数据库执行 show audit_set_parameter;
    2.登录数据库，创建schema,设置模式搜索路径，再删除schema
    3.登录数据库，查看审计日志SELECT * FROM pg_query_audit('$start_time',
    '$end_time');时间设在最接近登录数据库的时间
Expect      :
    1.返回默认值0
    2.执行成功
    3.未查询到set操作的信息
History     :
    modified：2024/06/28 by 30044860 audit_set_parameter的默认值改为0，优化用例
"""
import unittest
import time
import os
from yat.test import Node
from yat.test import macro
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonSH import Constant
from testcase.utils.Logger import Logger


class Auditing(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.userNode = Node('PrimaryDbUser')
        self.common = Common()
        self.constant = Constant()

    def test_security(self):
        text = '----step1.1:查看audit_set_parameter参数默认值----'
        self.logger.info(text)
        excute_cmd1 = f'show audit_set_parameter;'
        msg1 = self.sh_primy.execut_db_sql(excute_cmd1)
        self.logger.info(msg1)
        self.common.equal_sql_mdg(msg1, 'audit_set_parameter', '0', '(1 row)',
                                  flag='1')

        text = '----step1.2:设置参数audit_set_parameter=1----'
        self.logger.info(text)
        guc_res = self.sh_primy.execute_gsguc('reload',
                      self.constant.GSGUC_SUCCESS_MSG,
                      'audit_set_parameter=1',dn_path = macro.DB_INSTANCE_PATH)
        self.logger.info(guc_res)

        text = '----step2:创建schema----'
        self.logger.info(text)
        start_time_msg = self.sh_primy.execut_db_sql('SELECT sysdate;')
        start_time = start_time_msg.splitlines()[2].strip()
        time.sleep(5)
        sql_cmd2 = 'CREATE SCHEMA ds;SET SEARCH_PATH TO ds, public;' \
                   'DROP SCHEMA ds;'
        msg2 = self.sh_primy.execut_db_sql(sql_cmd2)
        self.logger.info(msg2)
        self.assertNotIn('ERROR', msg2, '执行失败：' + text)
        text = '----step3:查看审计日志----'
        self.logger.info(text)
        time.sleep(5)
        end_time_msg = self.sh_primy.execut_db_sql('SELECT sysdate;')
        end_time = end_time_msg.splitlines()[2].strip()
        sql_cmd3 = f'select * from pg_query_audit(\'{start_time}\',\
                   \'{end_time}\');'
        excute_cmd3 = f'source {macro.DB_ENV_PATH};' \
                      f'gsql -d {self.userNode.db_name} -p' \
                      f' {self.userNode.db_port} -c "{sql_cmd3}"'
        msg3 = self.userNode.sh(excute_cmd3).result()
        self.logger.info(msg3)
        self.assertIn('SET SEARCH_PATH TO ds, public', msg3, '执行失败' + text)

    def tearDown(self):
        text = '----恢复数据----'
        self.logger.info(text)
        self.logger.info(text)
        guc_res = self.sh_primy.execute_gsguc('reload',
                      self.constant.GSGUC_SUCCESS_MSG,
                      'audit_set_parameter=0', dn_path=macro.DB_INSTANCE_PATH)
        self.logger.info(guc_res)
        self.assertTrue(guc_res, '执行失败：' + text)
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
